package cn.com.spotty.sqldemo.jdbc;

import cn.com.spotty.sqldemo.bean.News;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class SQLHelper {

	// 获取 mysql 连接池
	public Connection getConnection() {
		String driver = "com.mysql.jdbc.Driver";
		String url = "jdbc:mysql://127.0.0.1:3306/jiandan_db";
		String username = "root";
		String password = "war1096409085";
		Connection conn = null;
		try {
			Class.forName(driver); //classLoader,加载对应驱动
			conn = DriverManager.getConnection(url, username, password);
		} catch (ClassNotFoundException | SQLException e) {
			e.printStackTrace();
		}
		return conn;
	}

	// 执行单条 SQL 语句
	public List<News> executeSQL(Connection conn, String sql) throws Exception {
		Statement statement = null;
		statement = conn.createStatement();
		List<News> news = new ArrayList<>();
		ResultSet rs = statement.executeQuery(sql);
		while(rs.next()){
			// 通过字段检索
			String news_title = rs.getString("news_title");
			String news_desc = rs.getString("news_desc");
			News temp = new News();
			temp.setNewsTitle(news_title);
			temp.setNewsDesc(news_desc);
			news.add(temp);
		}
		conn.close();
		return news;
	}
}
